2015-09-29- 22011 - Spec - Sales rep EPM #CrystalReportDevelopment

SPECIFICATIONS

22011 - Sales rep EPM

Requirement Summary

Need to create a new EPM report with Sales rep data.

Admin Info

Purpose	Need to create a new EPM report with Sales rep data.
Requested By	Jack Mehltretter
Spec Created By	Dhananjay G
Spec Created Date	09/29/2015
Spec QA by	Uday Kumar P
Objects	Sales Rep EPM
Document Status	Complete

Estimates

SNo	Activity	Estimation in Hours
1	Research & Analysis	15
2	Documentation	12
3	Development/ Config	110
4	Unit test in DEV	20
5	Unit test in QUA	22
6	BEx Queries & transports	46
	TOTAL	225

References

Prior Tickets

None

Documents

None

Spec Changes

SNo	Change Details	Requested By	Requested On	Notes if any
01	Need to add Navigational Sales Manager (0SALES_OFF) to all EPM ProductMix BEx Queries.	Saravanan Lakshmanan	10/06/2015	Change requested through IM.
02	Need to exclude 'Not Assigned' values on Sales Manager for all EPM ProductMix BEx Queries.	Saravanan Lakshmanan	10/13/2015	Change requested through Email.
03	Need to change the logic so that for Licenses and Silhouette groups show the top 10 and all others based on this year total. Need to Add COPA Customer Object (0CUSTOMER) in all EPM BEx Queries.	Mark Harris	10/16/2015	Change requested through Email.
04	Need to implement the contribution margin logic torestrict it to two characters its percentage...hence it should be 99 in the full decmal place 99.99,-99.99 is the maximum value allowed	Saravanan Lakshmanan	10/19/2015	Change requested through IM.
05	License name & Silhouette name adjust length of the textbox.	Saravanan Lakshmanan	11/10/2015	Change requested through Email.
06	Need to remove the filter on Sales Manager (0CUST_SALES_0SALES_OFF) object in all the EPM Product Mix BEx Queries	Saravanan Lakshmanan	11/16/2015	Change requested through IM.
07	Need to suppress the MTD data throughout the scorecard, formatting issues and budget validation with BEx queries.	Saravanan Lakshmanan	11/24/2015	Change requested through Email.
08	Need to implement budget values for COPA customers with comparision of BPC Customers	Saravanan Lakshmanan	12/01/2015	Change requested on weekly meeting time.
09	Need to remove suppressed monthly sub reports completely in Sales Rep Scroecard.	Saravanan Lakshmanan	01/21/2016	Change requested through Email.

Functional Requirement

Purpose/WHY:

Need to create a new EPM Report with Sales rep data.

In Scope:

Need to create a new EPM Report with Sales rep data for Global region.

Out of Scope:

Regional versions for Sales rep Scorecard.

Solution Summary

BEx Query Changes:

1. Need to add Sales rep as navigational attribute to BPC Multiprovider ZBPC_MP.
2. Need to add Customer Sales object to ZBPC_PLAN infoprovider.
3. Need to change BEx Queries by adding Sales rep as navigational attribute.
4. Need to create new transport requests and collect all objects into separate transport requests.
5. Need to validate in BPQ and after successful testing transport them to BPP.
6. Need to map the BEx queries to new Crystal EPM report.
7. Need to add Sales Manager (0SALES_OFF) to all the EPM Product Mix BEx Queries.
8. Need to create new transport request and collect all the BEx query changes.
9. Need to exclude 'Not Assigned' values on Sales Manager for all EPM BEx Queries.
10. Need to restrict 0CUST_SALES_0SALES_OFF characteristic in filter pane with 'Not Assigned'.
11. Need to create a new transport request and collect all the BEx Query changes.
12.Need to add Customer infoobject(0CUSTOMER) to all EPM Product Mix BEx Queries.
13.Need to create new transport request and collect all the BEx Query Changes.
14. Need to remove filter on Sales Manager (0CUSTOMER_SALES_0SALES_OFF) Object in all EPM ProdMix BEx Queries as this change effects the budget value in other EPM'S
15. Need to create a new transport request and collect all the BEx Query Changes.

Test Plan

SNo	Test Scenario	Results
1	Check if navigational sales rep is updated correctly into the BPC Multiprovider.	Navigational sales rep should be updated in BPC Multiprovider.
2	Check if all the BEx Queries related to EPM Sales Rep are added with navigational sales rep.	All the BEx Queries related to EPM Sales Rep report should be added with Navigational sales rep successfully.
3	Check if all the BEx Queries related to EPM Sales Rep are added with navigational Sales Manager.	All the BEx Queries related to EPM Sales Rep are added with navigational Sales Manager.
4	Check if all the BEx queries related to Sales Rep EPM are excluded with 'Not Assigned' on Sales Manager Navigational attribute.	All the BEx queries related to Sales Rep EPM are excluded with 'Not Assigned' on Sales Manager Navigational attribute.
5	Need to Check Whether Customer infoobject(0CUSTOMER) is added to all EPM Product Mix BEx Queries.	Customer infoobject(0CUSTOMER) is added to all EPM Product Mix BEx Queries.
6	Check if the report is updated with the BEx query changes & Material Group is replaced with navigational Sales Rep in the report.	Reports should be updated with BEx query changes & navigational sales rep should be added in place of Material group in the report.
7	Check if all the objects are collected into the transport request.	All the objects should be collected successfully into the transport request.
8	Check whether filter removed on Sales Manager (0CUST_SALES_0SALES_OFF) in all EPM Product Mix BEx Queries.	All the BEx Queries related to Sales Rep EPM were removed with filter on Sales Manager (0CUST_SALES_0SALES_OFF)

Crystal Changes:

1. Need to update the Sales Rep EPM Report with BEx query changes & need to add navigational sales rep in place of Material group in the report.
2. Need to check if Material Group is replaced with navigational Sales Rep in all subreports of the main report.
3. Need to update the Sales Rep EPM report with newly added query changes.
4. Need to add new group Sales Manager (0SALES_OFF) in the report.
5. Need to validate with BEx queries after completing the development changes.
6. Need to change the logic so that for Licenses and Silhouette groups show the top 10 and all others based on this year total.
7. Need to change the logic so that for Customer groups show the top 15 and all others based on this year total.
8. Need to implement the contribution margin logic to restrict it to two characters its percentage, hence it should be 99 in the full decmal place 99.99,-99.99 is the maximum value allowed.
9. Need to suppress the MTD data throughout the scorecard, formatting issues and budget validation.
10. Need to display budget values for all customer subreports.
11. Need to remove suppressed monthly sub reports completely in Sales Rep Scroecard.

Test Plan

Test ID	Test Scenario	Expected Results
1	Report Format.	Should be as per NEC crystal standard checklist.
2	Execute Crystal Report in Crystal Designer tool after changes.	Report should execute successfully in Crystal Designer tool.
3	Schedule the Report in NEC BI Launch Pad.	Report should execute successfully in NEC BI Launch Pad.

Solution Details

BEx Query Changes:

1. Need to add Sales rep as navigational attribute to BPC Multiprovider ZBPC_MP.
2. Need to add Customer Sales object to ZBPC_PLAN infoprovider.
3. Need to change BEx Queries by adding Sales rep as navigational attribute.
4. Need to create new transport requests and collect all objects into separate transport requests.
5. Need to validate in BPQ and after successful testing transport them to BPP.
6. Need to map the BEx queries to new Crystal EPM report.
7. Need to add Sales Manager (0SALES_OFF) to all the EPM ProdMix BEx Queries.
8. Need to create new transport request and collect all the BEx query changes.
9. Need to do unit testing in BPD, then transport to BPQ after successful testing transport to BPP.
10. Need to restrict 0CUST_SALES_0SALES_OFF characteristic in filter pane for 'Not Assigned' values.
11. Need to create new transport request and collect all the BEx Query changes.
12.Need to add Customer Object (0CUSTOMER) to all EPM Product Mix BEx Queries.
13.Need to create new transport request and collect all the BEx Query Changes.
14. Need to remove filter on Sales Manager (0CUSTOMER_SALES_0SALES_OFF) Object in all EPM ProdMix BEx Queries as this change effects the budget value in other EPM'S
15. Need to create a new transport request and collect all the BEx Query Changes.

Crystal Changes:

1. Need to update the Sales Rep EPM Report with BEx query changes & need to add navigational sales rep in place of Material group in the report.
2. Need to check if Material Group is replaced with navigational Sales Rep in all sub reports of the main report.
3. Need to update the Sales Rep EPM report with newly added query changes.
4. Need to add new group Sales Manager (0SALES_OFF) in the report.
5. Need to validate with BEx queries after completing the development changes.
6. Need to change the logic so that for Licenses and Silhouette groups show the top 10 and all others based on this year total.
7. Need to change the logic so that for Customer groups show the top 15 and all others based on this year total.
8. Need to implement the contribution margin logic to restrict it to two characters its percentage in Sales Rep EPM.
9. Need to suppress the MTD data, formatting issues and budget validation with BEx throughout the scorecard.
10. Need to display budget values for all customer subreports.
11. Need to remove suppressed monthly sub reports completely in Sales Rep Scroecard.

Issues

1. Since there is no Budget data for Sales Rep in the BPC source, the data for all the sub reports is shown into 'Others' category as per the formula written. The formula is that if budget value is '0, then values go into 'Others'. So we have removed the formula for 'others' category & data is now shown even though there are no budget values as seen in screenshot below:

2. Since there is no Budget data for Sales Manager from the BPC source, it is assigned to 'Not Assigned' value in Sales Manager as seen in screenshot below. This was fixed by excluding the '#' values for Sales Manager in BEx queries of this report.
Not Assigned in Sales Manager.jpg